/**
 * Copyright (c) 2017,安徽皖通科技股份有限公司 All rights reserved.
 * @data:2017年10月9日
 * @author:MaShibo
 */
package com.wttech.dzzf.process.dao;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.wtkj.dao.impl.BaseDataTemplate;
import com.wtkj.vo.dzzf.share.staticvalue.AgencyType;
import com.wttech.dzzf.process.model.issuer.Agency;
import com.wttech.dzzf.process.model.issuer.Hall;

import cn.com.taiji.sdk.model.comm.protocol.basics.issuer.AgencyUploadRequest;
import cn.com.taiji.sdk.model.comm.protocol.basics.issuer.HallUploadRequest;
import cn.com.taiji.sdk.model.comm.protocol.basics.issuer.IssuerUploadRequest;
import cn.com.taiji.sdk.model.comm.protocol.basics.issuer.MobileServiceUploadRequest;
import cn.com.taiji.sdk.model.comm.protocol.basics.issuer.OnlineUploadRequest;
import cn.com.taiji.sdk.model.comm.protocol.basics.issuer.TerminalUploadRequest;

/**
 * 发行服务设施数据Dao实现
 * 
 * @date 2017年10月9日
 * @author MaShibo
 */
@Repository
public class ProcessIssuerInfoDao extends BaseDataTemplate {
    
    
    @Autowired
    public ProcessIssuerInfoDao(@Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    public List<Agency> queryAgencyInfos() {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT J.CHANNELID AS ID, ");
        sql.append("        J.NAME, ");
        sql.append("        J.DIRECTOR AS CONTACT, ");
        sql.append("        J.OFFICEPHONE AS TEL, ");
        sql.append("        TO_CHAR(J.STARTON, 'yyyy-MM-dd') AS STARTTIME, ");
        sql.append("        TO_CHAR(J.ENDON, 'yyyy-MM-dd') AS ENDTIME ");
        sql.append("   FROM JG_ORGANIZATION J ");
        sql.append("  WHERE J.AGENCYTYPE = ? ");
        sql.append("    AND J.CHANNELID IS NOT NULL ");
        sql.append("  ORDER BY J.CODE ");
        return jdbcTemplate.query(sql.toString(), new Object[] { AgencyType.AGENCY.getValue() },
                BeanPropertyRowMapper.newInstance(Agency.class));
    }
    
    public List<Hall> queryHallInfos() {
        StringBuilder sql = new StringBuilder();
        sql.append("  SELECT J.ORGID, ");
        sql.append("         SUBSTR(J.CODE, 0, 4) AS TOPCLASS, ");
        sql.append("         P.CHANNELID AS AGENCYID, ");
        sql.append("         J.CITYID, ");
        sql.append("         J.COUNTYID, ");
        sql.append("         J.NAME, ");
        sql.append("         J.DIRECTOR AS CONTACT, ");
        sql.append("         J.OFFICEPHONE AS TEL, ");
        sql.append("         J.ADDRESS, ");
        sql.append("         J.ORGTYPE, ");
        sql.append("         J.SERVICEITEMS, ");
        sql.append("         J.BUSINESSHOURS, ");
        sql.append("         TO_CHAR(J.STARTON, 'yyyy-MM-dd') AS STARTTIME, ");
        sql.append("         TO_CHAR(J.ENDON, 'yyyy-MM-dd') AS ENDTIME ");
        sql.append("    FROM JG_ORGANIZATION J ");
        sql.append("    LEFT JOIN JG_ORGANIZATION P ");
        sql.append("      ON J.PORGID = P.ORGID ");
        sql.append("   WHERE LENGTH(J.CODE) > 4 ");
        sql.append("     AND SUBSTR(J.CODE, 0, 4) IN ");
        sql.append("         (SELECT X.TOPCLASS ");
        sql.append("            FROM XXJH_TOPCLASSISSUERAGENT X ");
        sql.append("           WHERE X.ISINUSE = 'Y') ");
        sql.append("     AND J.CITYID IS NOT NULL ");
        sql.append("     AND J.COUNTYID IS NOT NULL ");
        sql.append("     AND J.CODE NOT IN ('612601001') ");
        sql.append("   ORDER BY J.CODE ");
        return jdbcTemplate.query(sql.toString(), BeanPropertyRowMapper.newInstance(Hall.class));
    }
    
    public void dropAndCreateHallSeq() {
        final String dropSql = " DROP SEQUENCE BS_HALL_SEQ ";
        jdbcTemplate.execute(dropSql);
        
        final StringBuilder createSql = new StringBuilder();
        createSql.append(" CREATE SEQUENCE BS_HALL_SEQ INCREMENT BY 1 ");
        createSql.append(" START WITH 1 MAXVALUE 9999 NOCYCLE CACHE 20 ");
        jdbcTemplate.execute(createSql.toString());
    }
    
    public Integer queryHallSeqNo() {
        final String sql = "SELECT BS_HALL_SEQ.NEXTVAL FROM DUAL ";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }
    
    public void resetHallSeqNo() {
        final String update = " UPDATE JG_ORGANIZATION A SET A.HALLSEQNO = NULL ";
        jdbcTemplate.execute(update);
    }
    
    public Integer queryMaxCountySeqNo(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT NVL(MAX(J.HALLSEQNO), 0) ");
        sql.append("   FROM JG_ORGANIZATION J ");
        sql.append("  WHERE J.CHANNELID IS NOT NULL ");
        sql.append("  AND SUBSTR(J.CHANNELID, 1, 15) = ? ");
        return jdbcTemplate.queryForObject(sql.toString(),
                new Object[] {id}, Integer.class);
    }
    
    public List<IssuerUploadRequest> queryIssuerReqInfos() {
        final String sql = "SELECT * FROM BS_ISSUER ";
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(IssuerUploadRequest.class));
    }
    
    public List<AgencyUploadRequest> queryAgencyReqInfos() {
        final String sql = "SELECT * FROM BS_AGENCY ";
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(AgencyUploadRequest.class));
    }
    
    public List<HallUploadRequest> queryHallReqInfos() {
        final String sql = "SELECT * FROM BS_HALL ";
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(HallUploadRequest.class));
    }
    
    public List<MobileServiceUploadRequest> queryMobileServiceReqInfos() {
        final String sql = "SELECT * FROM BS_MOBILESERVICE ";
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(MobileServiceUploadRequest.class));
    }
    
    public List<TerminalUploadRequest> queryTerminalReqInfos() {
        final String sql = "SELECT * FROM BS_TERMINAL ";
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(TerminalUploadRequest.class));
    }
    
    public List<OnlineUploadRequest> queryOnlineReqInfos() {
        final String sql = "SELECT * FROM BS_ONLINE ";
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(OnlineUploadRequest.class));
    }
}
